package server;



import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import com.mysql.jdbc.PreparedStatement;
import common.MyFile;

/**
 * 
 * handles any request made the users, from the database
 *
 */
public class DataBase 
{
	/** connection to database */
	public Connection conn;
	/** holds, connection to database information */
	public String[] set=new String[4]; //Gil
	/** array of string sent to client in case of an error */
    public String[] err=new String[3]; //Gil
	
    /** establish connection to database */
	public Boolean makeConnection()
	{
		try 
		{
          Class.forName("com.mysql.jdbc.Driver").newInstance();
         
      } catch (Exception ex) {/* handle the error*/}
      try {
		conn = DriverManager.getConnection(set[0],set[1],set[2]);
		return true;
	} catch (SQLException e) 
		{
		 err[2]=e.getMessage();
		 return false;
		
		}
	}

	/** 
	 * retrieves all books in reader's cart, eliminates books that were removed from the catalog
	 * @param st holds commands for server and database, from the client 
	 * @return String[]
	 */
	public String[] retrieve_cart(String[] st)
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]="retrieve_cart";
		result[2]="";
		
		if(!isFrozen(st[0]))
		{
			try 
			{
	            
	    	    makeConnection();
	            Statement stmt,stmt1,stmt2;
	            stmt=conn.createStatement();
	            stmt1=conn.createStatement();
	            stmt2=conn.createStatement();
	            ResultSet rs =stmt.executeQuery("SELECT books.title,cart.language,cart.format,catalog.price FROM books, cart, catalog,users WHERE catalog.serial=cart.serial and books.serial=catalog.serial and users.id='"+st[2]+"' and users.id=cart.id");
	            ResultSet rs1=stmt1.executeQuery("SELECT books.title,books.serial FROM books WHERE books.serial IN(SELECT cart.serial FROM cart WHERE NOT cart.serial IN (SELECT catalog.serial FROM catalog,cart WHERE catalog.serial=cart.serial))");
	            while(rs.next())
	            {
	            	result[2]+=rs.getString(1)+"#"+rs.getString(2)+"#"+rs.getString(3)+"#"+rs.getString(4)+"*";
	            }
	            result[2]+="%";
	            if(rs1.next())
	            {
	            	result[2]+=rs1.getString(1);	
	            	stmt2.executeUpdate("DELETE FROM cart WHERE serial='"+rs1.getString(2)+"'");
	            }
	            while(rs1.next())
	            {
	            	stmt2.executeUpdate("DELETE FROM cart WHERE serial='"+rs1.getString(2)+"'");
	            	result[2]+=" , "+rs1.getString(1);
	            }
	            rs.close();
	            rs1.close();
	            conn.close();
	            return result;
	            
			} catch (SQLException ex) 
	   	    	{/* handle any errors*/
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				return result;
	   	    	}
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	            
	 	}
	/**
	 * check membership status
	 * @param st holds commands for server and database, from the client 
	 * @return String[]
	 */
	public String[] checkMembership(String[] st)
	{
		String expDate = null;
		String[] result=new String[3];
		result[0]=st[0];
		result[1]=st[1];
		result[2]="";
		Calendar cal = Calendar.getInstance();
		Integer day,month,year;
		
		if(!isFrozen(st[0]))
		{
			try 
			{
	            
	    	  	makeConnection();
	            Statement stmt;
	            stmt=conn.createStatement();
	            ResultSet rs =stmt.executeQuery("select expiration from membership where id='"+st[2]+"'");
	            while(rs.next())
	            {
	            	expDate=rs.getString(1);
	            }
	            if(expDate!=null)
	            {
	            	result[2]="expMembership";
	            	String[] date=expDate.split("\\.");
	            	String temp;
	            	temp=date[0];
					int i=0,j=0,k=0;
					i=temp.indexOf("1");
					j=temp.indexOf("2");
					k=temp.indexOf("3");
					if((i>j && j!=-1) || i==-1) i=j;
					if((i>k && k!=-1) || i==-1) i=k;
					date[0]=temp.substring(i);
	            	day=Integer.parseInt(date[0]);
	            	month=Integer.parseInt(date[1]);
	            	year=Integer.parseInt(date[2]);
	            	if(year>cal.get(Calendar.YEAR)%100)
	        			result[2]="valid";
	        		else if(cal.get(Calendar.YEAR)%100==year)
	        			if(month>cal.get(Calendar.MONTH)+1)
	        				result[2]="valid";
	        			else if(month==cal.get(Calendar.MONTH)+1)
	        				if(day>=cal.get(Calendar.DAY_OF_MONTH))
	        					result[2]="valid";
	            	if(result[2].equals("valid"))
	            	{
	            		result[2]+="."+day.toString();
	            		result[2]+="."+month.toString();
	            		result[2]+="."+year.toString();
	            	}
	            }
	            else
	            	result[2]="noMembership";
	            
	            rs.close();
	            conn.close();
	            return result;
	            
			} catch (SQLException ex) 
				{/* handle any errors*/
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				return result;
				}
		}
		else
		{
			result[1]="frozen";
			return result;
		}
			
	            
	 	}
	/**
	 * purchase all books that are currently in the cart. removes from cart, books that were removed from catalog
	 * @param st holds commands for server and database, from the client 
	 * @return String[]
	 */
	public String[] gPurchase(String[] st)
	{
		String[] result=new String[3];
		String[] books=st[3].split("\\*");
		String[] lan=st[4].split("\\*");
		result[0]=st[0];
		result[1]="purchase";
		result[2]="";
		String query="SELECT catalog.serial,catalog.price,books.title FROM catalog,books,language WHERE (";
		String query2;
		Calendar cal = Calendar.getInstance();
		Integer day=cal.get(Calendar.DAY_OF_MONTH);
		Integer month=cal.get(Calendar.MONTH)+1;
		Integer year=cal.get(Calendar.YEAR);
		String date=year.toString()+"."+month.toString()+"."+day.toString();
		
		if(!isFrozen(st[0]))
		{
			try 
			{
	            String id = st[2];
	            Boolean isIdTaken=false;
	    	  	makeConnection();
	            Statement stmt;
	            for(int i=0;i<books.length-1;i++)
	            	query+="(title='"+books[i]+"' and language.language='"+lan[i]+"') or ";
	            query+="(title='"+books[books.length-1]+"' and language.language='"+lan[lan.length-1]+"'))";
	            query+=" and books.serial=catalog.serial and books.serial=language.serial";
	            stmt=conn.createStatement();
	            ResultSet rs =stmt.executeQuery(query);
	            query=new String();
	            if(rs.next())
	            {
		            query="SELECT * FROM cart WHERE serial='"+rs.getString(1)+"'";
		            result[2]+=rs.getString(3)+"*";
		            while(rs.next())
		            {
		            	query+=" or serial='"+rs.getString(1)+"'";
		            	result[2]+=rs.getString(3)+"*";
			        }
		            stmt=conn.createStatement();
		            ResultSet rs1 =stmt.executeQuery(query);
		            PreparedStatement ps= (PreparedStatement) conn.prepareStatement("insert into purchased values(?,?,?,?,?,?,?)");
		            stmt=conn.createStatement();
		           
		            
		            while(rs1.next())
		            {
		            	query2=new String();
		            	query2="SELECT * FROM catalog WHERE serial='"+rs1.getString(2)+"'";
		            	ResultSet rs2 =stmt.executeQuery(query2);
		            	if(rs2.next())
		            	{
			            	ps.setString(1, rs1.getString(1));
			            	ps.setString(2, rs1.getString(2));
			            	ps.setString(3, date);
			            	ps.setString(4, rs1.getString(4));
			            	ps.setString(5, rs1.getString(5));
			            	ps.setString(6, rs1.getString(6));
			            	ps.setString(7, rs1.getString(7));
			            	ps.executeUpdate();
			            	if(isIdTaken==false)
			            	{
			            		id=rs1.getString(1);
			            		isIdTaken=true;
			            	}
		            	}
		            }
		            rs.close();
		            rs1.close();
	            }
	            stmt=conn.createStatement();
	            stmt.executeUpdate("DELETE FROM `goodreading`.`cart` WHERE id='"+id+"'");
	            conn.close();
	            return result;
	            
			} catch (SQLException ex) 
	   	    	{/* handle any errors*/
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				return result;
	   	    	}
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	            
	 	}
	/**
	 * remove a specific book from the cart.
	 * @param st holds commands for server and database, from the client 
	 * @return String[]
	 */
	public String[] gRemove(String[] st)
	{
		String[] result=new String[3];
		String book=st[3];
		result[0]=st[0];
		result[1]="purchase";
		result[2]="";
		String query="SELECT serial FROM books WHERE ";
		
		if(!isFrozen(st[0]))
		{
			try 
			{
	            
	    	  	makeConnection();
	            Statement stmt;
	            query+="title='"+book+"'";
	            stmt=conn.createStatement();
	            ResultSet rs =stmt.executeQuery(query);
	            query=new String();
	            rs.next();
	            query="SELECT * FROM cart WHERE serial='"+rs.getString(1)+"'";
	            stmt=conn.createStatement();
	            ResultSet rs1 =stmt.executeQuery(query);
	            stmt=conn.createStatement();
	            String str;
	            rs1.next();
	            str="DELETE FROM `goodreading`.`cart` WHERE id='"+rs1.getString(1)+"' and serial='"+rs1.getString(2)+"'";
	            stmt.executeUpdate(str);
	            rs.close();
	            rs1.close();
	            conn.close();
	            return result;
	            
			} catch (SQLException ex) 
				{/* handle any errors*/
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				return result;
				}
		}
		else
		{
			result[1]="frozen";
			return result;
		}
			
	            
	 	}
	/**
	 * updates reader's membership expiration date, after the reader have purchased a membership
	 * @param st holds commands for server and database, from the client 
	 * @return String[]
	 */
		public String[] gupdate(String[] st)
		{
			String[] result=new String[3];
			result[0]=st[0];
			result[1]="update Membership";
			result[2]="OK";
	
			if(!isFrozen(st[0]))
			{ 
				try 
				{
		            
		    	  	makeConnection();
		            Statement stmt;
		            stmt=conn.createStatement();
		            if(st[4].equals("valid") || st[4].equals("Have Expired"))
		            	stmt.executeUpdate("update membership set expiration='"+st[3]+"' where id='"+st[2]+"'");
		            else
		            	stmt.executeUpdate("INSERT INTO `goodreading`.`membership` (`id`, `expiration`) VALUES ('"+st[2]+"', '"+st[3]+"')");
		            conn.close();
		            return result;
		          		            
				} catch (SQLException ex) 
		   	    	{/* handle any errors*/
					System.out.println("SQLState: " + ex.getSQLState());
					System.out.println("VendorError: " + ex.getErrorCode());
					return result;
		   	    	}
			}
			else
			{
				result[1]="frozen";
				return result;
			}
		            
		 	}
		/**
		 * retrieves a list of books, that the reader have purchased and can now write a review on. 
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
		
		public String[] gbooks(String[] st)
		{
			String[] result=new String[3];
			result[0]=st[0];
			result[1]=st[1];
			result[2]="";
			
			if(!isFrozen(st[0]))
			{
				try 
				{
		            
		    	  	makeConnection();
		            Statement stmt;
		            stmt=conn.createStatement();
		            ResultSet rs =stmt.executeQuery("SELECT DISTINCT books.title FROM books, purchased WHERE purchased.id= '"+st[2]+"' and books.serial=purchased.serial");
		            while(rs.next())
		            {
		            	result[2]+=rs.getString(1)+"*";
		            }
		            rs.close();
		            conn.close();
		            return result;
		            
				} catch (SQLException ex) 
					{/* handle any errors*/
					System.out.println("SQLState: " + ex.getSQLState());
					System.out.println("VendorError: " + ex.getErrorCode());
					return result;
					}
			}
			else
			{
				result[1]="frozen";
				return result;
			}
		            
		 }
		/**
		 * add a reader review. the review will be published after the librarian will verify it. 
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */

		public String[] gAddReview(String[] st)
		{
			String[] result=new String[3];
			result[0]=st[0];
			result[1]=st[1];
			result[2]="WRONG";
			String serial="";
			String rID;
			Integer temp=0,temp1=0;
			
			if(!isFrozen(st[0]))
			{

				try 
				{
		            
		    	  	makeConnection();
		            Statement stmt,stmt1,stmt2;
		            stmt=conn.createStatement();
		            stmt1=conn.createStatement();
		            stmt2=conn.createStatement();
		            ResultSet rs =stmt.executeQuery("SELECT books.serial FROM books WHERE books.title='"+st[3]+"'");
		            ResultSet rs1=stmt1.executeQuery("SELECT MAX(unposted.rID) FROM unposted"); 
		            ResultSet rs2=stmt2.executeQuery("SELECT MAX(posted.rID) FROM posted");
		            rs.next();
		            if(rs.getString(1)!=null)
		            	serial=rs.getString(1);
		            rs2.next();
		            if(rs2.getString(1)!=null)
		            		temp=Integer.parseInt(rs2.getString(1));
		            rs1.next();
		            if(rs1.getString(1)!=null)
		                temp1=Integer.parseInt(rs1.getString(1));
		            if(temp1>temp) temp=temp1;
		            temp++;
		            rID=temp.toString();
		            stmt=conn.createStatement();
		            stmt.executeUpdate("INSERT INTO unposted values('"+st[2]+"','"+serial+"','"+st[4]+"','"+rID+"')");
		            result[2]="OK";
		            rs.close();
		            rs1.close();
		            rs2.close();
		            conn.close();
		            return result;
		            
				} catch (SQLException ex) 
		   	    	{/* handle any errors*/
					System.out.println("SQLState: " + ex.getSQLState());
					System.out.println("VendorError: " + ex.getErrorCode());
					return result;
		   	    	}
				
			}
			else
			{
				result[1]="frozen";
				return result;
			}     
		 }
		/**
		 * add a book to the reader's shopping cart. 
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
		public String[] gAddToCart(String[] st)
		{
			String[] result=new String[3];
			result[0]=st[0];
			result[1]=st[1];
			result[2]="WRONG";
			String[] format;
			
			
			if(!isFrozen(st[0]))
			{
				try 
				{
		            
		    	  	makeConnection();
		            Statement stmt,stmt1;
		            stmt=conn.createStatement();
		            stmt1=conn.createStatement();
		            ResultSet rs =stmt.executeQuery("SELECT purchased.language FROM purchased WHERE purchased.serial='"+st[4]+"' and purchased.id='"+st[5]+"'");
		            while(rs.next())
		            {
		            	if(rs.getString(1).contains(st[3]))
		            	{
		            		result[2]="PURCHASED";
		            		break;
		            	}
		            	
		            }
		            if(!result[2].equals("PURCHASED"))
		            {
		            	ResultSet rs1=stmt1.executeQuery("SELECT cart.language FROM cart WHERE cart.serial='"+st[4]+"' and cart.id='"+st[5]+"'");
		            	while(rs1.next())
		            	{
		            		if(rs1.getString(1).contains(st[3]))
		            		{
		            			result[2]="CART";
		            			break;
		            		}
		            	}
		            	if(!result[2].equals("CART"))
		            	{
		            			format=new String[3];
		            			for(int j=0;j<3;j++)
		            				format[j]="0";
		            			if(st[2].contains("pdf")) format[0]="1";
		            			if(st[2].contains("doc")) format[1]="1";
		            			if(st[2].contains("fb2")) format[2]="1";
		            			
		            			stmt.executeUpdate("INSERT INTO `goodreading`.`cart` (`id`, `serial`, `format`, `language`, `isPDF`, `isDOC`, `isFB2`) VALUES ('"+st[5]+"', '"+st[4]+"', '"+st[2]+"', '"+st[3]+"','"+format[0]+"','"+format[1]+"','"+format[2]+"')");
		            			result[2]="OK";
		            			rs1.close();
		            	}
			        }
		             
		     
		            conn.close();
		            return result;
		            
				} catch (SQLException ex) 
		   	    	{/* handle any errors*/
					System.out.println("SQLState: " + ex.getSQLState());
					System.out.println("VendorError: " + ex.getErrorCode());
					return result;
		   	    	}
			}
			else
			{
				result[1]="frozen";
				return result;
			}
		            
		 }
		/**
		 * Retrieves all the books purchased by the reader, for him to select which book he wants to download to his computer
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
		
		public String[] gGetPurchase(String[] st)
		{
			String[] result=new String[3];
			result[0]=st[0];
			result[1]=st[1];
			result[2]="";
			
			if(!isFrozen(st[0]))
			{
				try 
				{
		            
		    	  	makeConnection();
		            Statement stmt;
		            stmt=conn.createStatement();
		            ResultSet rs =stmt.executeQuery("select purchased.serial,books.title,purchased.language,purchased.date,purchased.isPDF,purchased.isDOC,purchased.isFB2 FROM books,purchased WHERE books.serial=purchased.serial and purchased.id='"+st[2]+"'");
		            while(rs.next())
		            {
		            	result[2]+=rs.getString(1)+"#"+rs.getString(2)+"#"+rs.getString(3).toString()+"#"+rs.getString(4)+"#"+rs.getString(5)+"#"+rs.getString(6)+"#"+rs.getString(7)+"*";
		            }
		            rs.close();
		            conn.close();
		            return result;
		            
				} catch (SQLException ex) 
		   	    	{/* handle any errors*/
						System.out.println("SQLState: " + ex.getSQLState());
						System.out.println("VendorError: " + ex.getErrorCode());
						return result;
		   	    	}   
			}
			else
			{
				result[1]="frozen";
				return result;
			}
		            
		 	}
		/**
		 * send the actual file to the client 
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
		public MyFile getFile(String[] st)
		{
			
			 MyFile result= new MyFile(st[3]);
			 String LocalfilePath=set[3]+"\\"+st[3];
			 result.ip=st[0];
			
			 if(!isFrozen(st[0]))
				{

				 	try{

				      File newFile = new File (LocalfilePath);
				      		      
				      byte [] mybytearray  = new byte [(int)newFile.length()];
				      FileInputStream fis = new FileInputStream(newFile);
				      BufferedInputStream bis = new BufferedInputStream(fis);			  
				      
				      result.initArray(mybytearray.length);
				      result.setSize(mybytearray.length);
				      
				      bis.read(result.getMybytearray(),0,mybytearray.length);
				      return result;		      
				 		}
				 	catch (Exception e) {
				 		System.out.println("Error send (Files)msg) to Server");
				 		return null;
				 	}
				}
			 else
			 {
				 System.out.println("Error send (Files)msg) to Server");
			 		return null;
			 }
		  }
	
		/**
		 * restore book from the removed books from catalog table(removedfc) back to catalog table. 
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
	public String[] resBook(String[] dbRData)
	{
		String[] result=new String[3];
		String query = new String();
		String serial =null;
		String price =null;
		result[0]=dbRData[0];
		result[1]="resBook";
		result[2]="";
		if(!isFrozen(dbRData[0]))
		{

			try 
			{           
	    	  	makeConnection();
	    	  	Statement stmt;
	            stmt=conn.createStatement();

	            query="";
	            query = "SELECT r.serial,r.price FROM goodreading.removedfc r WHERE r.serial='"+dbRData[2]+"' ";
	    		ResultSet rs =stmt.executeQuery(query);
	            while(rs.next())
	            {
	            	serial=rs.getString(1);
	            	price=Integer.toString(rs.getInt(2));
	            }
	            rs.close();
	            query="";
	            query = "INSERT INTO goodreading.catalog VALUES ('"+serial+"', '"+price+"')";
	    		stmt.executeUpdate(query);
	    		query="";
	            query = "DELETE FROM goodreading.removedfc WHERE serial='"+serial+"'";
	    		stmt.executeUpdate(query);
	    		result[2]="OK";
	    		if (result[2].equals(""))
	            	result[2]="error";
	            
	            rs.close();
	       
	    		conn.close();
	  	      	
	  	      return result;
	  	            
	  	      } catch (SQLException ex) 
	  	   	    {/* handle any errors*/
	  	          System.out.println("SQLState: " + ex.getSQLState());
	  	          System.out.println("VendorError: " + ex.getErrorCode());
	  	          result[2]="error";
	  	          return result;
	  	          }
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	}
	/**
	 * Retrieve all book details from all related tables
	 * @param st holds commands for server and database, from the client 
	 * @return String[]
	 */
	public String[] getDB(String[] dbData)
	{
		String[] result=new String[3];
		String query = new String();
		result[0]=dbData[0];
		result[1]="getDB";
		result[2]="";
		if(!isFrozen(dbData[0]))
		{

		  try 
	      {           
	    	  	makeConnection();
	    	  	Statement stmt;
	            stmt=conn.createStatement();

	            query="";
	            query = "SELECT r.serial,r.title FROM goodreading.removedfc r ";
	    		ResultSet rs =stmt.executeQuery(query);
	            while(rs.next())
	            {
	            	result[2]+=rs.getString(1)+":";
	            	result[2]+=rs.getString(2)+"*";
	            }
	            if (result[2].equals(""))
	            	result[2]="error";
	            rs.close();
	    		conn.close();
	  	      return result;
	  	            
	  	      } catch (SQLException ex) 
	  	   	    {/* handle any errors*/
	  	          System.out.println("SQLState: " + ex.getSQLState());
	  	          System.out.println("VendorError: " + ex.getErrorCode());
	  	          result[2]="error";
	  	          return result;
	  	          }
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	}
	/**
	 * Retrieve book's reviews from posted table. 
	 * @param st holds commands for server and database, from the client 
	 * @return String[]
	 */
	public String[] greview(String[] review)
	{
		String[] result=new String[3];
		String query = new String();
		result[0]=review[0];
		result[1]="getreview";
		result[2]="";
		if(!isFrozen(review[0]))
		{
		  try 
	      {           
	    	  	makeConnection();
	    	  	Statement stmt;
	            stmt=conn.createStatement();

	            query="";
	            query = "SELECT u.name,p.review from goodreading.users u,goodreading.posted p WHERE p.serial='"+review[2]+"' and p.id=u.id ORDER BY u.name";
	    		ResultSet rs =stmt.executeQuery(query);
	            while(rs.next())
	            {
	            	result[2]+=rs.getString(1)+": ; ";
	            	result[2]+=rs.getString(2)+"*";
	            }
	            if (result[2].equals(""))
	            	result[2]="error";
	            rs.close();
	    		conn.close();
	  	      return result;
	  	            
	  	      } catch (SQLException ex) 
	  	   	    {/* handle any errors*/
	  	          System.out.println("SQLState: " + ex.getSQLState());
	  	          System.out.println("VendorError: " + ex.getErrorCode());
	  	          result[2]="error";
	  	          return result;
	  	          }  
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	}
	/**
	 * Search book according to the requested parameters, by the user, from the related tables.  
	 * @param st holds commands for server and database, from the client 
	 * @return String[]
	 */
	public String[] SearchR(String[] searchR)
	{
		String[] result=new String[3];
		String query = new String();
		boolean orflag=false;
		boolean andflag=false;
		int i=0;
		result[0]=searchR[0];
		result[1]="searchreview";
		result[2]="";
		if(!isFrozen(searchR[0]))
		{
		  try 
	      {           
	    	  	makeConnection();
	    	  	Statement stmt;
	            stmt=conn.createStatement();

	            query="";
	            query = "SELECT DISTINCT b.serial,b.title FROM goodreading.author a,goodreading.books b WHERE a.serial=b.serial and (";
	            if(searchR[5].equals("or"))
	            {
	            	if (!searchR[2].equals(""))
		            {
		            	query+= "b.title LIKE '%"+searchR[2]+"%'";
		            	orflag=true;
		            }
		    		if (!searchR[3].equals(""))
		    		{
		    			if (orflag)
		    				query+=" or";
		    			else
		    				orflag=true;
		    			query+= " a.aName LIKE '%"+searchR[3]+"%'";
		    		}
		    		if (!searchR[4].equals(""))
		    		{
		    			if (orflag)
		    				query+=" or";
		    			else
		    				orflag=true;
		    			query+= " b.keywords LIKE '%"+searchR[4]+"%'";
		    		}
	            }
	            else
	            {
	            	if (!searchR[2].equals(""))
		            {
		            	query+= "b.title = '"+searchR[2]+"'";
		            	andflag=true;
		            }
		    		if (!searchR[3].equals(""))
		    		{
		    			if (andflag)
		    				query+=" and";
		    			else
		    				andflag=true;
		    			query+= " a.aName = '"+searchR[3]+"'";
		    		}
		    		if (!searchR[4].equals(""))
		    		{
		    			if (andflag)
		    				query+=" and";
		    			else
		    				andflag=true;
		    			query+= " b.keywords = '"+searchR[4]+"'";
		    		}
	            }
	    		query+=")";
	    		ResultSet rs =stmt.executeQuery(query);
	            while(rs.next())
	            {
	            	result[2]+=rs.getString(1)+" : ";
	            	result[2]+=rs.getString(2)+"*";
					i++;
	            }
	            if (result[2].equals(""))
	            	result[2]="error";
	            rs.close();
	    		conn.close();
	  	      return result;
	  	            
	  	      } catch (SQLException ex) 
	  	   	    {/* handle any errors*/
	  	          System.out.println("SQLState: " + ex.getSQLState());
	  	          System.out.println("VendorError: " + ex.getErrorCode());
	  	          result[2]="error";
	  	          return result;
	  	          }  
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	}
	/**
	 * Search book according to the requested parameters, by the user, from the related tables.  
	 * @param st holds commands for server and database, from the client 
	 * @return String[]
	 */
	public String[] ySearch(String[] searchData) 
	{
		String[] result=new String[3];
		String[] serial = new String[10];
		Integer[] numofsearch = new Integer[10];
		result[0]=searchData[0];
		result[1]="searchbook";
		result[2]="";
		String query = new String();
		boolean orflag=false;
		boolean andflag=false;
		int i=0;
		PreparedStatement ps;

		if(!isFrozen(searchData[0]))
		{
	      try 
	      {           
	    	  	makeConnection();
	    	  	Statement stmt;
	            stmt=conn.createStatement();
	            query = "SELECT DISTINCT b.serial,b.title,b.numberOS FROM language l,author a,books b,bookforsubjectfield bf,catalog c WHERE bf.Bookid=b.serial and c.serial=b.serial and a.serial=c.serial and (";
	            if (searchData[14].equals("or"))
	    		{
		            if (!searchData[2].equals(""))
		            {
		            	query+= "b.title LIKE '%"+searchData[2]+"%'";
		            	orflag=true;
		            }
		    		if (!searchData[3].equals(""))
		    		{
		    			if (orflag)
		    				query+=" or";
		    			else
		    				orflag=true;
		    			query+= " a.aName LIKE '%"+searchData[3]+"%'";
		    		}
		    		if (!searchData[4].equals(""))
		    		{	if (orflag)
		    				query+=" or";
		    			else
		    				orflag=true;
		    			query+= " l.language LIKE '%"+searchData[4]+"%'";
		    		}
		    		if (!searchData[5].equals(""))
		    		{
		    			if (orflag)
		    				query+=" or";
		    			else
		    				orflag=true;
		    			query+= " b.keywords LIKE '%"+searchData[5]+"%'";
		    		}
		    		if (!searchData[6].equals(""))
		    		{
		    			if (orflag)
		    				query+=" or";
		    			else
		    				orflag=true;
		    			query+= " b.summary LIKE '%"+searchData[6]+"%'";
		    		}
		    		if (!searchData[7].equals(""))
		    		{
		    			if (orflag)
		    				query+=" or";
		    			else
		    				orflag=true;
		    			query+= " b.index1 LIKE '%"+searchData[7]+"%'";;
		    		}
		    		if (!searchData[11].equals(""))
		    		{
		    			if (orflag)
		    				query+=" or";
		    			else
		    				orflag=true;
		    			query+= " c.price = '"+searchData[11]+"'";;
		    		}
		    		if (!searchData[8].equals(""))
		    		{
		    			if (orflag)
		    				query+=" or";
		    			else
		    				orflag=true;
		    			query+= " b.isPdf = '"+searchData[8]+"'";;
		    		}
		    		if (!searchData[9].equals(""))
		    		{
		    			if (orflag)
		    				query+=" and";
		    			else
		    				orflag=true;
		    			query+= " b.isDoc = '"+searchData[9]+"'";;
		    		}
		    		if (!searchData[10].equals(""))
		    		{
		    			if (orflag)
		    				query+=" and";
		    			else
		    				orflag=true;
		    			query+= " b.isFb2 = '"+searchData[10]+"'";;
		    		}
		    	
		    		if (!searchData[12].equals(""))
		    		{
		    			if (orflag)
		    				query+=" and";
		    			else
		    				orflag=true;
		    			query+= " bf.Subject = '"+searchData[12]+"'";;
		    		}
		    		if (!searchData[13].equals(""))
		    		{
		    			if (orflag)
		    				query+=" and";
		    			else
		    				orflag=true;
		    			query+= " bf.Field = '"+searchData[13]+"'";;
		    		}
	    		}
	            else
	            {
	                if (!searchData[2].equals(""))
		            {
		            	query+= "b.title = '"+searchData[2]+"'";
		            	andflag=true;
		            }
		    		if (!searchData[3].equals(""))
		    		{
		    			if (andflag)
		    				query+=" and";
		    			else
		    				andflag=true;
		    			query+= " a.aName = '"+searchData[3]+"'";
		    		}
		    		if (!searchData[4].equals(""))
		    		{	if (andflag)
		    				query+=" and";
		    			else
		    				andflag=true;
		    			query+= " l.language = '"+searchData[4]+"'";
		    		}
		    		if (!searchData[5].equals(""))
		    		{
		    			if (andflag)
		    				query+=" and";
		    			else
		    				andflag=true;
		    			query+= " b.keywords = '"+searchData[5]+"'";
		    		}
		    		if (!searchData[6].equals(""))
		    		{
		    			if (andflag)
		    				query+=" or";
		    			else
		    				andflag=true;
		    			query+= " b.summary = '"+searchData[6]+"'";
		    		}
		    		if (!searchData[7].equals(""))
		    		{
		    			if (andflag)
		    				query+=" and";
		    			else
		    				andflag=true;
		    			query+= " b.index1 = '"+searchData[7]+"'";;
		    		}
		    		if (!searchData[11].equals(""))
		    		{
		    			if (andflag)
		    				query+=" and";
		    			else
		    				andflag=true;
		    			query+= " c.price = '"+searchData[11]+"'";;
		    		}
		    		if (!searchData[8].equals(""))
		    		{
		    			if (andflag)
		    				query+=" and";
		    			else
		    				andflag=true;
		    			query+= " b.isPdf = '"+searchData[8]+"'";;
		    		}
		    		if (!searchData[9].equals(""))
		    		{
		    			if (andflag)
		    				query+=" and";
		    			else
		    				andflag=true;
		    			query+= " b.isDoc = '"+searchData[9]+"'";;
		    		}
		    		if (!searchData[10].equals(""))
		    		{
		    			if (andflag)
		    				query+=" and";
		    			else
		    				andflag=true;
		    			query+= " b.isFb2 = '"+searchData[10]+"'";;
		    		}
		    	
		    		if (!searchData[12].equals(""))
		    		{
		    			if (andflag)
		    				query+=" and";
		    			else
		    				andflag=true;
		    			query+= " bf.Subject = '"+searchData[12]+"'";;
		    		}
		    		if (!searchData[13].equals(""))
		    		{
		    			if (andflag)
		    				query+=" and";
		    			else
		    				andflag=true;
		    			query+= " bf.Field = '"+searchData[13]+"'";;
		    		}
	            }
		    	query+=")";  	
	    		ResultSet rs =stmt.executeQuery(query);
	            while(rs.next())
	            {
	            	result[2]+=rs.getString(1)+" : ";
	            	serial[i]=rs.getString(1);
	            	result[2]+=rs.getString(2)+"*";
	            	numofsearch[i] = rs.getInt(3)+1;
					i++;
	            }
	            if (result[2].equals(""))
	            	result[2]="error";
	            rs.close();
	            for (int j=0;j<i;j++)
	            {
	            	ps = (PreparedStatement) conn.prepareStatement("UPDATE goodreading.books SET numberOS=? WHERE serial=?");
					ps.setInt(1, numofsearch[j]);
					ps.setString(2, serial[j]);
					ps.executeUpdate();
	            }
	            conn.close();
	      return result;
	            
	      } catch (SQLException ex) 
	   	    {/* handle any errors*/
	    	  result[2]="error";
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          }      
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	            
	}
	/**
	 * Retrieve all book details from all the related tables.  
	 * @param st holds commands for server and database, from the client 
	 * @return String[]
	 */
	public String[] retrieve_details(String[] st) 
	{
		
		String[] result=new String[3];
		result[0]=st[0];
		result[1]="retrieve_details";
		result[2]="";
		if(!isFrozen(st[0]))
		{
	      try 
	      {
	            
	    	  	
	            if (st[1].equals("retrieve_details"))
	            {
	            	makeConnection();
		            Statement stmt;
		            stmt=conn.createStatement();
	            	ResultSet rs =stmt.executeQuery("SELECT DISTINCT b.serial,b.title,b.summary,b.keywords,b.index1,b.isPdf,b.isDoc,b.isFb2,b.numberOS,c.price FROM books b,author a,language l,catalog c WHERE b.serial = '"+st[2]+"' and b.serial=a.serial and c.serial=a.serial and l.serial=c.serial");

		            while(rs.next())
		            {
		            	int i,num=0;
		            	for(i=1;i<11;i++)
		            	{
		            		if (i==10)
		            		{
		            			num = rs.getInt(i);
		    	            	result[2]+=Integer.toString(num)+"*";
		            		}
		            		else 
		            			result[2]+=rs.getString(i)+"*";
		            	}
		            	
		            }
		            rs.close();
		           
		            ResultSet rs2 =stmt.executeQuery("SELECT DISTINCT a.aName FROM books b,author a,language l WHERE b.serial = '"+st[2]+"' and b.serial=a.serial and l.serial=a.serial");
		            while(rs2.next())
		            {
		            	result[2]+=rs2.getString(1)+"-";
		            }
		            rs2.close();
		            conn.close();
	            }
	            else
	            {
	            	makeConnection();
		            Statement stmt;
		            stmt=conn.createStatement();
	            	ResultSet rs1 =stmt.executeQuery("SELECT DISTINCT l.language FROM books b,author a,language l,catalog c WHERE b.serial = '"+st[2]+"' and b.serial=a.serial and c.serial=a.serial and l.serial=c.serial");
		            while(rs1.next())
		            {
		            	result[2]+=rs1.getString(1)+"*";
		            }
		            rs1.close();
		            conn.close();
	            }
	            
	            return result;
	            
	      } catch (SQLException ex) 
	   	    {/* handle any errors*/
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          }    
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	}
	/**
	 * Update book's details according to the user's request.  
	 * @param st holds commands for server and database, from the client 
	 * @return String[]
	 */
	public String[] upDateDetails(String[] upDate) 
	{
		boolean andflag=false;
		String query1=null;
		String tempA=null;
		String [] tempAU=null;
		String tempL=null;
		String [] tempLA=null;
		String[] result=new String[3];
		result[0]=upDate[0];
		result[1]="update_details";
		result[2]="";
		if(!isFrozen(upDate[0]))
		{
	      try 
	      {
	            
	    	  	makeConnection();
	            Statement stmt;
	            stmt=conn.createStatement();
	            query1 = "UPDATE goodreading.books SET  ";
	            if (!upDate[2].equals(""))
	            {
	            	query1+= "serial = '"+upDate[2]+"'";
	            	andflag=true;
	            }
	    		if (!upDate[3].equals(""))
	    		{
	    			if (andflag)
	    				query1+=" ,";
	    			else
	    				andflag=true;
	    			query1+= " title = '"+upDate[3]+"'";
	    		}
	    		if (!upDate[4].equals(""))
	    		{	if (andflag)
	    				query1+=" ,";
	    			else
	    				andflag=true;
	    			query1+= " keywords = '"+upDate[6]+"'";
	    		}
	    		if (!upDate[5].equals(""))
	    		{
	    			if (andflag)
	    				query1+=" ,";
	    			else
	    				andflag=true;
	    			query1+= " summary = '"+upDate[7]+"'";
	    		}
	    		if (!upDate[6].equals(""))
	    		{
	    			if (andflag)
	    				query1+=" ,";
	    			else
	    				andflag=true;
	    			query1+= " index1 = '"+upDate[8]+"'";
	    		}
	    		if (!upDate[7].equals(""))
	    		{
	    			if (andflag)
	    				query1+=" ,";
	    			else
	    				andflag=true;
	    			query1+= " isPdf = '"+upDate[9]+"'";;
	    		}
	    		if (!upDate[9].equals(""))
	            {
	    			if (andflag)
	    				query1+=" ,";
	    			else
	    				andflag=true;
	    			query1+= "isDoc = '"+upDate[10]+"'";
	            	
	            }
	    		if (!upDate[9].equals(""))
	            {
	    			if (andflag)
	    				query1+=" ,";
	    			else
	    				andflag=true;
	    			query1+= "isFb2 = '"+upDate[11]+"'";
	            	
	            }
	    		query1+=" WHERE serial = '"+upDate[2]+"'";
	    		stmt.executeUpdate(query1);
	            
	        	stmt.executeUpdate("DELETE FROM goodreading.author WHERE serial='"+upDate[2]+"'");
	        	query1="";
	    		tempA = upDate[4];
	    		tempAU = tempA.split("\\,");
	    		for (int j=0;j<tempAU.length;j++)
	    		{
	    			query1="";
	    			query1 = "INSERT INTO goodreading.author VALUES ( ";
	    			query1+= "'"+upDate[2]+"', ";
	            	query1+= "'"+tempAU[j]+"' )";
	            	stmt.executeUpdate(query1);
	    		}
	    		
	    		stmt.executeUpdate("DELETE FROM goodreading.language WHERE serial='"+upDate[2]+"'");
	        	query1="";
	    		tempL = upDate[5];
	    		tempLA = tempL.split("\\,");
	    		for (int j=0;j<tempLA.length;j++)
	    		{
	    			query1="";
	    			query1 = "INSERT INTO goodreading.language VALUES ( ";
	    			query1+= "'"+upDate[2]+"', ";
	            	query1+= "'"+tempLA[j]+"' )";
	            	stmt.executeUpdate(query1);
	    		}
	    		
	    		stmt.executeUpdate("DELETE FROM goodreading.catalog WHERE serial='"+upDate[2]+"'");
	        	query1="";
	   			query1 = "INSERT INTO goodreading.catalog VALUES ( ";
	    		query1+= "'"+upDate[2]+"', ";
	           	query1+= "'"+upDate[12]+"' )";
	            stmt.executeUpdate(query1);
	    		
	    		
	    		result[2]="OK";
	            conn.close();
	            return result;
	            
	      } catch (SQLException ex) 
	   	    {/* handle any errors*/
	          result[2] = "error";
	    	  System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          }
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	}
	/**
	 * Add new book to all related tables in data-base.    
	 * @param st holds commands for server and database, from the client 
	 * @return String[]
	 */
	public String[] add_new_book(String[] addbook)
	{
		int i=0;
		String query1=null;
		String query2=null;
		String query3=null;
		String query4=null;
		String query5=null;
		String tempA=null;
		String [] tempAU=null;
		String tempL=null;
		String [] tempLA=null;

		String[] result=new String[3];
		result[0]=addbook[0];
		result[1]="add_new_book";
		result[2]="";
		if(!isFrozen(addbook[0]))
		{
	      try 
	      {
	            
	    	  	makeConnection();
	            Statement stmt;
	            stmt=conn.createStatement();
	            query1 = "INSERT INTO goodreading.books VALUES (  ";
	            for ( i=2;i<10;i++)
	            	query1+= "'"+addbook[i]+"', ";
	            query1+= " 0) ";
	    		stmt.executeUpdate(query1);
	            
	    		tempA = addbook[12];
	    		tempAU = tempA.split("\\,");
	    		for (int j=0;j<tempAU.length;j++)
	    		{
	    			query2="";
	    			query2 = "INSERT INTO goodreading.author VALUES (  ";
	    			query2+= "'"+addbook[2]+"', ";
	            	query2+= "'"+tempAU[j]+"' )";
	            	stmt.executeUpdate(query2);
	    		}
	    		
	    		tempL = addbook[11];
	    		tempLA = tempL.split("\\,");
	    		for (int j=0;j<tempLA.length;j++)
	    		{
	    			query3="";
	    			query3 = "INSERT INTO goodreading.language VALUES (  ";
	    			query3+= "'"+addbook[2]+"', ";
	            	query3+= "'"+tempLA[j]+"' )";
	            	stmt.executeUpdate(query3);
	    		}
	    		
	    		query4 = "INSERT INTO goodreading.catalog VALUES (  ";
	            query4+= "'"+addbook[2]+"', ";
	            query4+= "'"+addbook[10]+"') ";
	    		stmt.executeUpdate(query4);
	    		
	    		query5 = "INSERT INTO goodreading.bookforsubjectfield VALUES (  ";
	            query5+= "'"+addbook[14]+"', ";
	            query5+= "'"+addbook[13]+"', ";
	            query5+= "'"+addbook[2]+"') ";
	    		stmt.executeUpdate(query5);
	    		
	    		result[2]="OK";
	            conn.close();
	            return result;
	            
	      } catch (SQLException ex) 
	   	    {/* handle any errors*/
	    	  result[2]="error";
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          }
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	
	}
	/**
	 * Remove requested book from catalog table,and insert the book into remove from catalog table(removefc).    
	 * @param st holds commands for server and database, from the client 
	 * @return String[]
	 */
	public String[] remove_from_catalog(String[] remove_bookFC)
	{
		String query1=null;
		String query2=null;
		String query3=null;
		String temp=null;
		String price=null;
		String[] result=new String[3];
		result[0]=remove_bookFC[0];
		result[1]="remove_bookFC";
		result[2]="";
		if(!isFrozen(remove_bookFC[0]))
		{
	      try 
	      {
	            
	    	  	makeConnection();
	            Statement stmt;
	            stmt=conn.createStatement();

	            
	    		query2 = "SELECT b.title,c.price FROM goodreading.books b,goodreading.catalog c WHERE b.serial = '"+remove_bookFC[2]+"' and b.serial=c.serial"; 
	    		ResultSet rs =stmt.executeQuery(query2);
	    		while(rs.next())
	    		{
	    			temp = rs.getString(1);
	    			price=Integer.toString(rs.getInt(2));
	    		}
	    		
	            query1 = "DELETE FROM goodreading.catalog WHERE serial = '"+remove_bookFC[2]+"' ";
	            stmt.executeUpdate(query1);
	    		rs.close();
	    		
	    		query3 = "INSERT INTO goodreading.removedFC  VALUES ('"+remove_bookFC[2]+"','"+temp+"','"+price+"') "; 
	    		stmt.executeUpdate(query3);
	    		
	    		result[2]="OK";
	            conn.close();
	            return result;
	            
	      } catch (SQLException ex) 
	   	    {/* handle any errors*/
	    	  result[2]="error";
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          }
		}
		else
		{
			result[1]="frozen";
			return result;
		}
		
	}
	/**
	 * Check if the entered serial by the user exist in data-base.     
	 * @param st holds commands for server and database, from the client 
	 * @return String[]
	 */
	public String[] check(String[] st)
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]=st[1];
		result[2]="";
		
		Statement stmt1;
		
		if(!isFrozen(st[0]))
		{
			try
			{
				makeConnection();
			stmt1=conn.createStatement();
			ResultSet rs =stmt1.executeQuery("SELECT b.serial FROM goodreading.books b  WHERE b.serial = '"+st[2]+"'");
			if (rs.next())
				result[2]="exist";
			else
				result[2]="ok";
            rs.close();
			conn.close();
           
            
			} 
			catch (SQLException ex) 
			{/* handle any errors*/
				result[2]="error";
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
			return result;
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	}

	/**
	 * validate user information, and logs him in to the system if all of the information is ok
	 * @param st
	 * @return String[]
	 */
	
	public String[] Login(String[] st) 
	{
		String[] result=new String[3];
		result[0]=err[0]=st[0];
		result[1]=st[1];
		err[1]="FAIL";
		result[2]="WRONG";
		set[0]=st[4];
		set[1]=st[5];
		set[2]=st[6];
		set[3]=st[7];
		Boolean frz=false;
		
		
		if(!makeConnection()) return err;
		try 
			{
				Statement stmt;
				stmt=conn.createStatement();
				ResultSet rs =stmt.executeQuery("SELECT * FROM users WHERE username= '"+st[2]+"'"); // string needs to be between 'string'
				if(rs.next())
				{
					if(rs.getString(10).equals("1")) frz=true;
					if(rs.getString(2).equals(st[3])) 
					{
						if(rs.getString(9).equals("0"))
						{
							if(!frz)
							{
								result[2]="OK*"+rs.getString(1)+"*"+rs.getString(4)+"*"+rs.getString(5)+"*"+rs.getString(6)+"*"+rs.getString(7)+"*"+rs.getString(8)+"*"+rs.getString(10);
								stmt.executeUpdate("UPDATE users SET inSystem=1 WHERE username= '"+st[2]+"'");
							}
		    			
						}
						else
							result[2]="IN"; 
						if(frz && !result.equals("IN"))
							result[2]="FROZEN";
					}  	
				}
				rs.close();
				conn.close();
				return result;
			} 
			
			catch (SQLException e) 
			{
				e.printStackTrace();
				return result;
			}
			
	}

	/**
	 * log user out of the system
	 * @param st
	 * @return String[]
	 */

	public String[] Logout(String[] st) 
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]=st[1];
		result[2]="";
		
		Statement stmt;
		makeConnection();
		try
			{
				if(st.length>=4 && st[3]!=null)
					if(!st[3].equals("logedIn"))
					{
						stmt=conn.createStatement();
						stmt.executeUpdate("UPDATE users SET inSystem=0 WHERE id='"+st[2]+"'");
					}
					else {}
				else
				{
					stmt=conn.createStatement();
					stmt.executeUpdate("UPDATE users SET inSystem=0 WHERE id='"+st[2]+"'");
				}
				result[2]="QUIT";
				conn.close();
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			
			return result;

		
	}

		/**
		 * add a Field to DB with subject="General".
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
	public String[] addField(String[] st) 
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]="addField";
		result[2]="";
		if(!isFrozen(st[0]))
		{

			try 
			{	            
	    	 makeConnection();
	         Statement stmt;
	         stmt=conn.createStatement();        
	         ResultSet rs =stmt.executeQuery("SELECT * FROM subjectforfield WHERE Field= '"+st[2]+"' "); 
	         if(rs.next())
			 {	        	 
	        	 if(rs.getString(1).equals(st[2]) )
	        	 {
			       	 result[2]="Exists";
	        	 }
			 }
	         else
	 	     {	 
	 	       	stmt.executeUpdate("INSERT into subjectforfield VALUES ('"+st[2]+"', 'General')");
	 		    result[2]="OK";  
	 	     }  				 
			 rs.close();	         
	         conn.close();
	         return result;	         
			}  catch (SQLException ex) 
				{/* handle any errors*/
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          }  
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	 	}
	
	/**
		 * Removes a Field from DB .
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
	public String[] removeField(String[] st) 
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]="RemoveField";
		result[2]="";
		if(!isFrozen(st[0]))
		{
			try 
			{	            
	    	 makeConnection();
	         Statement stmt;
	         stmt=conn.createStatement();
	         ResultSet rs =stmt.executeQuery("SELECT * FROM bookforsubjectfield WHERE Field= '"+st[2]+"' "); 
	         if(rs.next())
			 {	        	 
	        	 if(rs.getString(1).equals(st[2]))
	        	 {
			       	result[2]="Exists";			       
	        	 }
			 }
	         else
	         {
		         stmt.executeUpdate("DELETE FROM subjectforfield WHERE Field= '"+st[2]+"' ");		
		         result[2]="OK";
	         }
	         conn.close();
 	       	 return result;	         
			}  catch (SQLException ex) 
	   	    {/* handle any errors*/
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          } 

		}
				else
				{
					result[1]="frozen";
					return result;
				}
	 	}
	/**
		 * Adds a Subject to DB .
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
	public String[] addSubject(String[] st) 
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]="addSubject";
		result[2]="";
		if(!isFrozen(st[0]))
		{
			try 
			{	            
	    	 makeConnection();
	         Statement stmt;
	         stmt=conn.createStatement();        
	         ResultSet rs =stmt.executeQuery("SELECT * FROM subjectforfield WHERE Field= '"+st[3]+"' and subject='"+st[2]+"' "); 
	         if(rs.next())
			 {	        	 
	        	 if(rs.getString(1).equals(st[3]) && rs.getString(2).equals(st[2]))
	        	 {
			       	 result[2]="Exists";
	        	 }
			 }
	         else
	 	     {	 
	 	       	stmt.executeUpdate("INSERT into subjectforfield VALUES ('"+st[3]+"', '"+st[2]+"')");
	 		    result[2]="OK";  
	 	     }  				 
			 rs.close();	         
	         conn.close();
	         return result;	         
	      }  catch (SQLException ex) 
	   	    {/* handle any errors*/
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          } 
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	 	}
	/**
		 * Removes a Subject from DB .
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
	public String[] removeSubject(String[] st) 
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]="RemoveSubject";
		result[2]="";
		if(!isFrozen(st[0]))
		{
			try 
			{	            
	    	 makeConnection();
	         Statement stmt;
	         stmt=conn.createStatement();
	         ResultSet rs =stmt.executeQuery("SELECT * FROM bookforsubjectfield WHERE Field= '"+st[3]+"' and subject='"+st[2]+"' "); 
	         if(rs.next())
			 {	        	 
	        	 if(rs.getString(1).equals(st[3]) && rs.getString(2).equals(st[2]))
	        	 {
			       	result[2]="Exists";			       
	        	 }
			 }
	         else
	         {
		         stmt.executeUpdate("DELETE FROM subjectforfield WHERE Field= '"+st[3]+"' and subject='"+st[2]+"' ");		
		         result[2]="OK";
	         }
	         conn.close();
 	       	 return result;	         
			}  catch (SQLException ex) 
	   	    {/* handle any errors*/
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          }    	

		}
				else
				{
					result[1]="frozen";
					return result;
				}
	 	}
	/**
		 * Retreives Fields from DB : all fields OR Fields related to a specified subject.
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
	public String[] getField(String[] st) 
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]="getFields";
		if(st[1].equals("YgetFields"))  
				result[2]=" *";
		else result[2]="";		
		if(!isFrozen(st[0]))
		{

			try 
			{	            
	    	 makeConnection();
	         Statement stmt;
	         stmt=conn.createStatement();
	         ResultSet rs;	       
	         if (st[1].equals("getFieldsSubject") ||st[1].equals("YgetFieldsSubject") ||st[1].equals("getFieldsSubject1")) 
	        	  rs=stmt.executeQuery("SELECT distinct subjectforfield.Field FROM subjectforfield WHERE Subject='"+st[2]+"' ");
	         else	 
	        	 rs =stmt.executeQuery("SELECT distinct subjectforfield.Field FROM subjectforfield");
	         while(rs.next())
	         {
	         	result[2]+=rs.getString(1)+"*";
	         }	         
	         rs.close();
	         conn.close();
	         return result;	         
			}  catch (SQLException ex) 
			{/* handle any errors*/
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				return result;
				}   

		}
				else
				{
					result[1]="frozen";
					return result;
				}
	 	}
		/**
		 * Retreives all Subjects from DB.
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
	public String[] getSubjects(String[] st) 
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]="getSubjects";
		if(st[1].equals("getSubjects2")|| st[1].equals("getSubjects1"))
			result[2]=" *";
	else result[2]="";
	if(!isFrozen(st[0]))
	{

		try 
	      {	            
	    	 makeConnection();
	         Statement stmt;
	         stmt=conn.createStatement();	        
	         ResultSet resu =stmt.executeQuery("SELECT distinct subjectforfield.subject FROM subjectforfield");
	         while(resu.next())
	         {
	         	result[2]+=resu.getString(1)+"*";
	         }
	         resu.close();
	         conn.close();
	         return result;	         
	      }  catch (SQLException ex) 
	   	    {/* handle any errors*/
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          }  
	}
	else
	{
		result[1]="frozen";
		return result;
	}
}
		/**
		 * Retreives unposted Review Count from DB.
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
	public String[] unpostedReviewCount(String[] st) 
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]="getReviewCount";		
		result[2]="";
		if(!isFrozen(st[0]))
		{

			try 
			{	            
	    	 makeConnection();
	         Statement stmt;
	         stmt=conn.createStatement();	
	         ResultSet resu =stmt.executeQuery("SELECT COUNT(*) FROM unposted");
	         resu.next();      	
		     result[2]+=resu.getString(1);		     
	         resu.close();
	         conn.close();
	         return result;	         
			}  catch (SQLException ex) 
				{/* handle any errors*/
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				return result;
	          }   
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	 }
	/**
		 * Confirms/Removes an unposted review : deletes its from "unposted" and if confirmed, insert it to "posted"
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
	public String[] ConfirmReview(String[] st) 
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]=st[1];
		result[2]="";
		if(!isFrozen(st[0]))
		{

			try 
			{	            
				makeConnection();
				Statement stmt;
	         stmt=conn.createStatement();
	         if(st[1].equals("ConfirmReview"))
	        	 stmt.executeUpdate("INSERT into posted VALUES ('"+st[5]+"', '"+st[2]+"', '"+st[3]+"', '"+st[4]+"')");
	         stmt.executeUpdate("DELETE FROM unposted WHERE rID= '"+st[5]+"' ");	
	 		 conn.close();
	         return result;	         
				}  catch (SQLException ex) 
				{/* handle any errors*/
					System.out.println("SQLState: " + ex.getSQLState());
					System.out.println("VendorError: " + ex.getErrorCode());
					return result;
				}   
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	 	}
	/**
		 * Retreives unposted Review List from DB.
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
	public String[] getReviews(String[] st) 
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]="getUnpostedReviews";		
		result[2]="";
		if(!isFrozen(st[0]))
		{
			try 
			{	            
	    	 makeConnection();
	    	 Statement stmt,stmt2,stmt3;
	         stmt=conn.createStatement();
	         stmt2=conn.createStatement();
	         stmt3=conn.createStatement();	         
	         ResultSet resu =stmt.executeQuery("SELECT * FROM unposted");	         
	         ResultSet bookResu;
	         ResultSet userResu;
	         if(resu.next())
	         {
	        	 resu.previous();
		         while(resu.next())
		         {
		        	 bookResu=stmt2.executeQuery("SELECT * FROM books WHERE serial='"+resu.getString(2)+"' ");
		        	 bookResu.next();
		        	 userResu=stmt3.executeQuery("SELECT * FROM users WHERE id='"+resu.getString(1)+"' ");
		        	 userResu.next();
		        	 result[2]+=bookResu.getString(2)+"-"+userResu.getString(3)+"- ("+resu.getString(4)+")*";
		         }
	         }
	         else result[2]="NoReviews";
	         resu.close();
	         conn.close();
	         return result;	         
			}  catch (SQLException ex) 
	   	    {/* handle any errors*/
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          }    
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	 	}
		/**
		 * Add Specified Subject and field for a selected book
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
	public String[] AddSubjectForBook(String[] st) 
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]="AddSubjectForBook";
		result[2]="";
		if(!isFrozen(st[0]))
		{
			try 
			{	            
	    	 makeConnection();
	         Statement stmt;
	         stmt=conn.createStatement();        
	         ResultSet rs =stmt.executeQuery("SELECT * FROM bookforsubjectfield WHERE Field= '"+st[2]+"' AND Subject='"+st[3]+"' AND Bookid='"+st[4]+"' ");  
	         if(rs.next())
			 {	        	 
	        	 if(rs.getString(1).equals(st[2]) && rs.getString(2).equals(st[3]) && rs.getString(3).equals(st[4]))
	        	 {
			       	 result[2]="Exists";
	        	 }
			 }
	         else
	 	     {	 
	 	       	stmt.executeUpdate("INSERT into bookforsubjectfield VALUES ('"+st[2]+"', '"+st[3]+"','"+st[4]+"')");
	 		    result[2]="OK";  
	 	     }  				 
			 rs.close();	         
	         conn.close();
	         return result;	         
			}  catch (SQLException ex) 
			{/* handle any errors*/
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          }    	
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	 	}
		/**
		 * Removes Specified Subject and field from a selected book
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
	public String[]RemoveSubjectForBook(String[] st)
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]=st[1];
		result[2]="";
		if(!isFrozen(st[0]))
		{
			try 
			{	            
	    	 makeConnection();
	         Statement stmt;
	         stmt=conn.createStatement();	         
	         stmt.executeUpdate("DELETE FROM bookforsubjectfield WHERE Field= '"+st[2]+"' AND Subject='"+st[3]+"' AND Bookid='"+st[4]+"' ");  
	 		 conn.close();
	         return result;	         
			}  catch (SQLException ex) 
	   	    {/* handle any errors*/
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          }    
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	 	}
	
	/**
		 * Retreives Specified Subject and field for a selected book
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */
	public String[] GetBooksSubjects(String[] st) 
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]="GetBooksSubjects";		
		result[2]="";
		if(!isFrozen(st[0]))
		{
			try 
			{	            
	    	 makeConnection();
	    	 Statement stmt,stmt2;
	         stmt=conn.createStatement();	        	         
	         stmt2=conn.createStatement();
	         ResultSet BookResu=stmt2.executeQuery("SELECT * FROM books WHERE serial='"+st[2]+"' ");
	         BookResu.next();
	         result[2]+=BookResu.getString(2)+"*";
	         ResultSet resu=stmt.executeQuery("SELECT * FROM bookforsubjectfield WHERE Bookid='"+st[2]+"' "); 
	         while(resu.next())	         {
	        	
	        	 result[2]+="   "+ resu.getString(1)+"  :  "+resu.getString(2)+"*";
	         }
	         resu.close();
	         conn.close();
	         return result;	         
			}  catch (SQLException ex) 
			{/* handle any errors*/
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          }    	  
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	 	}
	/**
		 * Retreives an  Unposted Review 
		 * @param st holds commands for server and database, from the client 
		 * @return String[]
		 */	
	public String[] ReadUnpostedReview(String[] st) 
	{
		String[] result=new String[3];
		result[0]=st[0];
		result[1]="ReadUnpostedReview";		
		result[2]="";
		if(!isFrozen(st[0]))
		{
			try 
			{	            
	    	 makeConnection();
	         Statement stmt;
	         stmt=conn.createStatement();	        
	         ResultSet resu =stmt.executeQuery("SELECT * FROM unposted WHERE rID='"+st[2]+"' ");
	         while(resu.next())
		     {
	        	 for(int i=1; i<5; i++)
		         result[2]+=resu.getString(i)+"*";
		     }
	         resu.close();
	         conn.close();
	         return result;	         
			}  catch (SQLException ex) 
				{/* handle any errors*/
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          }    	   
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	 }	
	

	/**
	 * search data about selected user
	 * @param search data about the selected user
	 * @return all data about selected user
	 */
	public String[] lSearchUseru(String[] search)
	{
		String[] result=new String[3];
		result[0]=search[0];
		result[1]=search[1];
		result[2]="";
		if(!isFrozen(search[0]))
		{
			try 
			{           
					makeConnection();
					Statement stmt;
					stmt=conn.createStatement();
					if(search[2]=="") return gSearchUseru(search);
					String query = "SELECT id,password,username,phoneNumber,address,age,name,authorazations,isFrozen From users WHERE username='" + search[2] + "'";
					ResultSet rs =stmt.executeQuery(query);
					if(rs.next())
					{
						for(int i=1;i<9;i++)
							result[2]+=rs.getString(i) +"*";
						if(rs.getString(9).equals("1"))
							result[2]+="yes#";
						else
							result[2]+="no#";
					}
					rs.close();
					conn.close();
					return result;
	            
				} catch (SQLException ex) 
				{/* handle any errors*/
					System.out.println("SQLState: " + ex.getSQLState());
					System.out.println("VendorError: " + ex.getErrorCode());
					return result;
				}
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	}
	
	/**
	 * Search for all user data to
	 * @param search data about the user
	 * @return data about all users
	 */
	public String[] gSearchUseru(String[] search)
	{
		String[] result=new String[3];
		result[0]=search[0];
		result[1]=search[1];
		result[2]="";
		if(!isFrozen(search[0]))
		{
			try 
			{           
					makeConnection();
	    	  		Statement stmt;
	    	  		stmt=conn.createStatement();	
	    	  		String query = "SELECT id,password,username,phoneNumber,address,age,name,authorazations,isFrozen From users";
	    	  		ResultSet rs =stmt.executeQuery(query);
	    	  		while(rs.next())
	    	  		{
	    	  			for(int i=1;i<9;i++)
	    	  				result[2]+=rs.getString(i) +"*";
	    	  			if(rs.getString(9).equals("1"))
	    	  				result[2]+="yes#";
	    	  			else
	    	  				result[2]+="no#";
	    	  		}
	    	  		rs.close();
	    	  		conn.close();
	    	  		return result;
	            
			} catch (SQLException ex) 
	   	    	{/* handle any errors*/
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				return result;
	   	    	}  
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	}

	/**
	 * Update the freeze status of a user	
	 * @param update data about the user
	 * @return data about the user
	 */
	public String[] lUpdateF(String [] update)
	{
		String[] result=new String[3];
		result[0]=update[0];
		result[1]=update[1];
		result[2]="";
		if(!isFrozen(update[0]))
		{
			try 
			{           
				makeConnection();
				Statement stmt;
	            stmt=conn.createStatement();	
	    		String query = "UPDATE goodreading.users SET authorazations='"+ update[3]+"' , isFrozen='"+ update[4]+"' WHERE username='" + update[2] + "'";
	            stmt.executeUpdate(query);
	            result[2]="OK";
	            conn.close();
	            return result;
	            
			} catch (SQLException ex) 
	   	    	{/* handle any errors*/
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				return result;
	   	    	}  
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	
	}
	/**
	 * Search for data to create reader report
	 * @param search data about the reader
	 * @return list of all purchased books
	 */
	public String[] lSearchUserReaderReport(String[] search)
	{
		String[] result=new String[3];
		result[0]=search[0];
		result[1]=search[1];
		result[2]="";
		if(!isFrozen(search[0]))
		{
			try 
			{           
	    	  	makeConnection();
	    	  	Statement stmt;
	            stmt=conn.createStatement();	
	    		String query = "SELECT books.serial,books.title, purchased.date FROM `goodreading`.`books`,`goodreading`.`users`,`goodreading`.`purchased` WHERE books.serial=purchased.serial and users.username='" + search[2] + "' and users.id=purchased.id;";
	            ResultSet rs =stmt.executeQuery(query);
	            while(rs.next())
	            {
	            	result[2]+=rs.getString(1) +"*"+rs.getString(2)+"*"+rs.getDate(3)+"%";
	            }
	            rs.close();
	            conn.close();
	            return result;
	            
			} catch (SQLException ex) 
	   	    	{/* handle any errors*/
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				return result;
	   	    	}  
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	
	}
	/**
	 * Search for data about a book for book popularity report
	 * @param search data about the wanted book
	 * @return data needed to create book popularity report : number of purchases
	 */
	public String[] lSearchBookp(String[] search)
	{
		String[] result=new String[3];
		result[0]=search[0];
		result[1]=search[1];
		result[2]="";
		if(!isFrozen(search[0]))
		{
			try 
			{           
	    	  	makeConnection();
	    	  	Statement stmt;
	            stmt=conn.createStatement();	
	    		String query = "select count(*) from `goodreading`.`purchased` where `purchased`.serial='" + search[2] + "'";
	            ResultSet rs =stmt.executeQuery(query);
	            while(rs.next())
	            {
	            	result[2]+=Integer.toString(rs.getInt(1)) +"*";
	            }
	            rs.close();
	            if(!result[2].equals(""))
	            {
	            	String query1 = "select count(*) from `goodreading`.`purchased` ";
	            	ResultSet rs1 =stmt.executeQuery(query1);
	            	while(rs1.next())
	            	{
	            		result[2]+=Integer.toString(rs1.getInt(1)) +"*";
	            	}
	            	result[2]+="%";
	            	rs1.close();
	            }
	            conn.close();
	            return result;
	            
			} catch (SQLException ex) 
	   	    	{/* handle any errors*/
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				return result;
	   	    	}  
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	
	}
	/**
	 * Search for data in selected date range for the histogram
	 * @param search data about the wanted book and date range
	 * @return data needed to create the histogram : date,number of searches ,number of purchases
	 */
	public String[] lSearchBooks(String[] search)
	{
		String query1;
		ResultSet rs1;
		String[] tmp,inDate,outDate;
		int in[],out[];
		in=new int[3];
		out=new int[3];
		String[] result=new String[3];
		result[0]=search[0];
		result[1]=search[1];
		tmp=search[2].split("\\*");
		inDate=tmp[1].split("\\/");
		outDate=tmp[2].split("\\/");
		for(int i=0;i<3;i++)
		{
			in[i]=Integer.parseInt(inDate[i]);
			out[i]=Integer.parseInt(outDate[i]);
		}
		result[2]="";
		if(!isFrozen(search[0]))
		{
			try 
			{           
	    	  	makeConnection();
	    	  	Statement stmt;
	            stmt=conn.createStatement();	
	    		String query = "select search.date from `goodreading`.`search` where search.bookid='" + tmp[0] + "'and search.date>='"+in[2]+"/" +in[0]+"/"+in[1]+  "' and search.date<= '"+out[2]+"/" +out[0]+"/"+out[1]+  "'";
	            ResultSet rs =stmt.executeQuery(query);
	            while(rs.next())
	            {
	            	result[2]+=(rs.getDate(1)) +"*";
	            }
	            rs.close();
	            if(!result[2].equals(""))
	            {
	            	result[2]+="%";
	            	query1 = "select purchased.date,count(*) from `goodreading`.`purchased` where purchased.serial='" + tmp[0] + "'and purchased.date>='"+in[2]+"/" +in[0]+"/"+in[1]+  "' and purchased.date<= '"+out[2]+"/" +out[0]+"/"+out[1]+  "' group by purchased.date";
	            	rs1 =stmt.executeQuery(query1);
	            	while(rs1.next())
	            	{
	            		result[2]+=rs1.getDate(1)+"*"+Integer.toString(rs1.getInt(2)) +"$";
	            	}
	            	result[2]+="%";
	            	rs1.close();
	            }
	            if(!result[2].equals(""))
	            {
	            	//result[2]+="%";
	            	query1 = "select search.date,search.num from `goodreading`.`search` where search.bookid='" + tmp[0] +"'and search.date>='"+in[2]+"/" +in[0]+"/"+in[1]+  "' and search.date<= '"+out[2]+"/" +out[0]+"/"+out[1]+  "' group by search.date";
	            	rs1 =stmt.executeQuery(query1);
	            	while(rs1.next())
	            	{
	            		result[2]+=rs1.getDate(1)+"*"+Integer.toString(rs1.getInt(2)) +"$";
	            	}
	            	result[2]+="%";
	            	rs1.close();
	            }
	            conn.close();
	            return result;
	            
			} catch (SQLException ex) 
	   	    	{/* handle any errors*/
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				return result;
	   	    	}    
		}
		else
		{
			result[1]="frozen";
			return result;
		}
	
	}
	/**
	 * update the database about books searches , this is make so the there will be data for the histogram
	 * @param data data about the books
	 * @return data about the books
	 */
	public String[] lSearchUpdate(String[] data)
	{
		int num;
		String[] date;
		Calendar cal = Calendar.getInstance();
	    SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yy");
	    date=sdf.format(cal.getTime()).split("\\/");
	    String[] result=new String[3];
	    String[] search=data[2].split("\\*");
		result[0]=data[0];
		result[1]=data[1];
		result[2]="";
		if(!isFrozen(search[0]))
		{
			try 
			{           
	    	  	makeConnection();
	    	  	Statement stmt;
	            stmt=conn.createStatement();	
	            ResultSet rs;
	            String query;
	            for(int i=0;i<search.length;i++)
	            	{
	            	query = "select search.num from `goodreading`.`search` where search.bookid='" + search[i] + "' and search.date='"+date[2]+"/"+date[0]+"/"+date[1] +"'group by search.date";
	            	rs =stmt.executeQuery(query);
	            	result[2]="";
	            	while(rs.next())
	            	{
	            		result[2]+=(rs.getInt(1)) ;
	            	}
	            	rs.close();
	            	if(!result[2].equals(""))
	            	{
	            		num=Integer.parseInt(result[2]);
	            		num++;
	            		query = "UPDATE `goodreading`.`search` SET `num`='"+num+"' WHERE `bookid`=" + search[i] + " and`date`='"+date[2]+"/"+date[0]+"/"+date[1] +"';";
	            		stmt.executeUpdate(query);
	            	}
	            	else
	            	{
	            		query="INSERT INTO `goodreading`.`search` (`bookid`, `date`, `num`) VALUES ('" + search[i] + "', '"+date[2]+"/"+date[0]+"/"+date[1] +"', '1')";
	            		stmt.executeUpdate(query);
	            	}
	            }
	            conn.close();
	            return result;
	     
	            
			} catch (SQLException ex) 
	   	    	{/* handle any errors*/
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				return result;
	   	    	}
		}
		else
		{
			result[1]="frozen";
			return result;
		}
			
	
	
	}
	/**
	 * Check if a User is in freeze status
	 * @param data data about the user
	 * @return true if the user is in freeze mode
	 */
	public Boolean isFrozen(String data)
	{
		Boolean result=false;
		try 
	      {           
	    	  	makeConnection();
	    	  	Statement stmt;
	            stmt=conn.createStatement();	
	            ResultSet rs;
	            String query;
	            int frozen=-1;
	            query = "SELECT users.isFrozen FROM `goodreading`.`users` WHERE users.id='"+ data +"'";
	            rs =stmt.executeQuery(query);
	            	
	            while(rs.next())
	           	{
	            	frozen=(rs.getInt(1)) ;
	           	}
	            rs.close();
	            conn.close();
	            if(frozen==1)
	            	result=true;
	            return result;
	     
	            
	      } catch (SQLException ex) 
	   	    {/* handle any errors*/
	          System.out.println("SQLState: " + ex.getSQLState());
	          System.out.println("VendorError: " + ex.getErrorCode());
	          return result;
	          }      
	
	
	}
	
}
